//=====================================================================
//
//  File:       AdoSource.cs
//  Summary:    Demonstrates a data flow source component. This component 
//              uses the Ado.Net connection manager to connect to SQL 
//              Server Databases. 
//              
//  Date:       6/15/2004
//
//---------------------------------------------------------------------
//
//  This file is part of the Microsoft SQL Server Code Samples.
//  Copyright (C) Microsoft Corporation.  All rights reserved.
//
//  This source code is intended only as a supplement to Microsoft
//  Development Tools and/or on-line documentation.  See these other
//  materials for detailed information regarding Microsoft code samples.
//
//  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
//  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 
//  THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//  PARTICULAR PURPOSE.
//
//===================================================================== 

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;

namespace Microsoft.Samples.SqlServer.Dts
{
    [DtsPipelineComponent(
        DisplayName = "Ado Source CS Sample",
        ComponentType = ComponentType.SourceAdapter,
        IconResource = "Microsoft.Samples.SqlServer.Dts.AdoSource.ico"
    )]
    public class AdoSourceSample : PipelineComponent
    {
        #region Members

        ArrayList columnInformation;
        private bool Cancel;
        bool isConnected;
        private OleDbConnection oledbConnection;
        private OleDbCommand oledbCommand;
        bool areOutputColumnsValid = true;
        bool areExternalMetaDataColumnsValid = true;
        bool doOutputColumnsMatchExternalMetaDataColumns;

        /// <summary>
        /// Struct that is populated with the index of each output column in the buffer,
        /// and the name of the column at the external data source.
        /// </summary>
        private struct ColumnInfo
        {
            public int BufferColumnIndex;
            public string ColumnName;
        }

        #endregion

        #region Design Time

        #region ProvideComponentProperties
        public override void ProvideComponentProperties()
        {
            // Support resetting the component.
            ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
            RemoveAllInputsOutputsAndCustomProperties();

            // Add the sql statment custom property
            IDTSCustomProperty90 sql = ComponentMetaData.CustomPropertyCollection.New();
            sql.Name = "SqlStatement";

            IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
            output.Name = "AdoSourceOutput";
            output.ExternalMetadataColumnCollection.IsUsed = true;

            // Add the connection manager.
            IDTSRuntimeConnection90 adoConnection = ComponentMetaData.RuntimeConnectionCollection.New();
            adoConnection.Name = "ADO Connection";
        }
        #endregion

        #region Validate
        /// <summary>
        /// Validate the component by checking the inputs, outputs, custom properties, and column metadata.
        /// </summary>
        /// <returns>A value from the DTSValidationStatus indicating the result of validation.</returns>
        [CLSCompliant(false)]
        public override DTSValidationStatus Validate()
        {
            IDTSOutput90 output = ComponentMetaData.OutputCollection[0];

            if (ComponentMetaData.InputCollection.Count != 0)
            {
                ComponentMetaData.FireError(0, ComponentMetaData.Name, "Has an input when no input should exist.", "", 0, out this.Cancel);
                return DTSValidationStatus.VS_ISCORRUPT;
            }

            if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager == null)
            {
                ComponentMetaData.FireError(0, ComponentMetaData.Name, "No ADO ConnectionManager specified.", "", 0, out this.Cancel);
                return DTSValidationStatus.VS_ISBROKEN;
            }

            // check for the sql statement
            IDTSCustomProperty90 sqlStatement = ComponentMetaData.CustomPropertyCollection["SqlStatement"];
            if (sqlStatement.Value == null || ((string)sqlStatement.Value).Length == 0)
            {
                ComponentMetaData.FireError(0, ComponentMetaData.Name, "SqlStatement not specified.", "", 0, out this.Cancel);
                return DTSValidationStatus.VS_ISBROKEN;
            }
            else if (this.isConnected)
            {
                // Validate the SqlStatement by attempting to retreive the schema table.
                this.GetSchemaDataTable();
            }

            if (ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count == 0)
            {
                return DTSValidationStatus.VS_NEEDSNEWMETADATA;
            }

            // Validate the output columns against the external metadata?
            if (ComponentMetaData.ValidateExternalMetadata)
            {
                // Does the output column collection match the columns at the data source?
                if (!this.AreOutputColumnsValid())
                {
                    // No, post a warning, and fix the errors in reinitializemetadata.
                    this.areOutputColumnsValid = false;
                    ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "The output columns do not match the external data source.", "", 0);
                    return DTSValidationStatus.VS_NEEDSNEWMETADATA;
                }

                if (!ComponentValidation.DoesExternalMetaDataMatchOutputMetaData(output))
                {
                    this.areExternalMetaDataColumnsValid = false;
                    ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "The ExternalMetaDataColumns do not match the output columns.", "", 0);
                    return DTSValidationStatus.VS_NEEDSNEWMETADATA;
                }

                this.areExternalMetaDataColumnsValid = true;
                this.areOutputColumnsValid = true;
            }
            else
            {
                // Don't validate the output columns against the external source,
                // instead validate them against the external meta data collection.
                // Do the output columns match the external metadata columns?
                if (!ComponentValidation.DoesOutputColumnMetaDataMatchExternalColumnMetaData(ComponentMetaData.OutputCollection[0]))
                {
                    // No, post a warning, and fix the errors in ReintializeMetaData.
                    this.doOutputColumnsMatchExternalMetaDataColumns = false;
                    ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "Output columns do not match external metadata.", "", 0);
                    return DTSValidationStatus.VS_NEEDSNEWMETADATA;
                }

                this.doOutputColumnsMatchExternalMetaDataColumns = true;
            }

            // Return base class validation result.
            return base.Validate();
        }
        #endregion

        #region AcquireConnections
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:DoNotPassLiteralsAsLocalizedParameters", MessageId = "System.Exception.#ctor(System.String)")]
        public override void AcquireConnections(object transaction)
        {
            if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)
            {
                // Convert the native IDTSConnectionManager90 to the managed ConnectionManager,
                // then retrieve the underlying Connection through the InnerObject.
                ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);
                ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

                // If the InnerObject is not an ConnectionManagerAdoNet, then
                // the cmado object is null.
                if (cmado == null)
                {
                    throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");
                }

                // Get the underlying connection object.
                this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

                if (this.oledbConnection == null)
                {
                    throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");
                }

                this.isConnected = true;
            }
        }
        #endregion

        #region ReleaseConnections
        public override void ReleaseConnections()
        {
            if (this.oledbConnection != null 
                && this.oledbConnection.State != ConnectionState.Closed)
            {
                this.oledbConnection.Close();
            }

            this.isConnected = false;
        }
        #endregion

        #region ReinitializeMetaData
        public override void ReinitializeMetaData()
        {
            IDTSOutput90 output = ComponentMetaData.OutputCollection[0];

            // If connected then validate the external metadata columns against 
            if (this.isConnected)
            {
                // If there are no output columns, then create them from the data source.
                if (output.OutputColumnCollection.Count == 0)
                {
                    this.CreateColumnsFromDataTable();
                }

                // Are the output columns valid based on the external data source columns?
                if (!this.areOutputColumnsValid)
                {
                    // No, fix them.
                    this.FixOutputColumns();
                    this.areOutputColumnsValid = true;
                }

                // Do the external metadata columns match the output columns?
                if (!this.doOutputColumnsMatchExternalMetaDataColumns)
                {
                    this.doOutputColumnsMatchExternalMetaDataColumns = true;

                    // nope, get rid of the external columns
                    output.ExternalMetadataColumnCollection.RemoveAll();

                    foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)
                    {
                        CreateExternalMetaDataColumn(output.ExternalMetadataColumnCollection, col);
                    }
                }

                if (!this.areExternalMetaDataColumnsValid)
                {
                    ComponentValidation.FixExternalMetaDataColumns(output);
                    this.areExternalMetaDataColumnsValid = true;
                }
            }
            else
            {
                if (!this.doOutputColumnsMatchExternalMetaDataColumns)
                {
                    ComponentValidation.FixOutputColumnMetaData(output);
                    this.doOutputColumnsMatchExternalMetaDataColumns = true;
                }
            }
        }
        #endregion

        #region MapOutputColumn
        /// <summary>
        /// 
        /// </summary>
        /// <param name="iOutputID"></param>
        /// <param name="iOutputColumnID"></param>
        /// <param name="iExternalMetadataColumnID"></param>
        /// <param name="bMatch"></param>
        /// <returns></returns>
        [CLSCompliant(false)]
        public override IDTSExternalMetadataColumn90 MapOutputColumn(int iOutputID, int iOutputColumnID, int iExternalMetadataColumnID, bool bMatch)
        {
            IDTSOutput90 output = ComponentMetaData.OutputCollection.GetObjectByID(iOutputID);
            IDTSOutputColumn90 outputColumn = output.OutputColumnCollection.GetObjectByID(iOutputColumnID);
            IDTSExternalMetadataColumn90 externalColumn = output.ExternalMetadataColumnCollection.GetObjectByID(iExternalMetadataColumnID);

            outputColumn.ExternalMetadataColumnID = externalColumn.ID;
            return externalColumn;
        }
        #endregion

        #region SetOutputColumnProperty
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:DoNotPassLiteralsAsLocalizedParameters", MessageId = "System.Exception.#ctor(System.String)"), CLSCompliant(false)]
        public override IDTSCustomProperty90 SetOutputColumnProperty(int outputID, int outputColumnID, string propertyName, object propertyValue)
        {
            if (propertyName == "DataSourceColumnName")
            {
                throw new Exception("The DataSourceColumnName can not be modified.");
            }

            return base.SetOutputColumnProperty(outputID, outputColumnID, propertyName, propertyValue);
        }
        #endregion

        #region SetOutputColumnDataTypeProperties
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:DoNotPassLiteralsAsLocalizedParameters", MessageId = "System.Exception.#ctor(System.String)"), CLSCompliant(false)]
        public override void SetOutputColumnDataTypeProperties(int iOutputID, int iOutputColumnID, DataType eDataType, int iLength, int iPrecision, int iScale, int iCodePage)
        {
            IDTSOutputColumn90 col = ComponentMetaData.OutputCollection.GetObjectByID(iOutputID).OutputColumnCollection.GetObjectByID(iOutputColumnID);

            // Reject changes to the data type since this is managed by the component
            // based on the external data source data type.
            if (eDataType != col.DataType)
            {
                throw new Exception("The column data type is set based on the external column. It cannot be changed.");
            }

            col.SetDataTypeProperties(eDataType, iLength, iPrecision, iScale, iCodePage);
        }

        #endregion

        #endregion

        #region Runtime

        #region PreExecute
        /// <summary>
        /// Called before PrimeOutput. Find and store the index in the buffer of each of the columns in the output, and the
        /// name of the column at the external data source. Create the command that retreives the data from the data source.
        /// </summary>
        public override void PreExecute()
        {
            this.columnInformation = new ArrayList();
            IDTSOutput90 output = ComponentMetaData.OutputCollection[0];

            foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)
            {
                ColumnInfo ci = new ColumnInfo();
                ci.BufferColumnIndex = BufferManager.FindColumnByLineageID(output.Buffer, col.LineageID);
                ci.ColumnName = (string)col.CustomPropertyCollection["DataSourceColumnName"].Value;
                this.columnInformation.Add(ci);
            }

            // Create, and configure the command.
            this.oledbCommand = this.oledbConnection.CreateCommand();
            this.oledbCommand.CommandType = CommandType.Text;
            this.oledbCommand.CommandText = (string)ComponentMetaData.CustomPropertyCollection["SqlStatement"].Value;
        }

        #endregion

        #region PrimeOutput
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1804:RemoveUnusedLocals", MessageId = "output"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
        {
            IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
            PipelineBuffer buffer = buffers[0];

            try
            {
                OleDbDataReader dataReader = this.oledbCommand.ExecuteReader();

                // Walk the rows in the DataReader, 
                // and add them to the output buffer.
                while (dataReader.Read())
                {
                    // Add a row to the output buffer.
                    buffer.AddRow();

                    for (int x = 0; x < this.columnInformation.Count; x++)
                    {
                        ColumnInfo ci = (ColumnInfo)this.columnInformation[x];
                        int ordinal = dataReader.GetOrdinal(ci.ColumnName);

                        if (dataReader.IsDBNull(ordinal))
                        {
                            buffer.SetNull(ci.BufferColumnIndex);
                        }
                        else
                        {
                            buffer[ci.BufferColumnIndex] = dataReader[ci.ColumnName];
                        }
                    }
                }

                // Notify the data flow that we are finished adding rows to the output.
            }
            catch
            {
            }
            finally
            {
                buffer.SetEndOfRowset();
            }
        }
        #endregion

        #endregion

        #region Helpers

        #region AreOutputColumnsValid
        private bool AreOutputColumnsValid()
        {
            // Get the output and the table schema.
            IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
            DataTable dataTable = this.GetSchemaDataTable();

            // If the two sources don't have the same number of columns 
            // the output columns are not valid.
            if (dataTable.Rows.Count != output.OutputColumnCollection.Count)
            {
                return false;
            }

            // Otherwise, walk the rows of the DataTable, and validate each column
            for (int x = 0; x < output.OutputColumnCollection.Count; x++)
            {
                // Get the output column, the data row containing the data source column information, 
                // and the custom property on the output column that contains the name of the 
                // column at the data source 
                IDTSOutputColumn90 column = output.OutputColumnCollection[x];
                IDTSCustomProperty90 datasourceColumnName = column.CustomPropertyCollection["DataSourceColumnName"];

                int index = ColumnExists(dataTable, (string)datasourceColumnName.Value);

                // Does the column exist, by name, in the table schema?
                if (index == -1 || !DataSourceColumnMatchesOutputColumn(column, dataTable.Rows[index]))
                {
                    // Fire a warning before returning
                    ComponentMetaData.FireWarning(0, ComponentMetaData.Name, "The output column " + column.IdentificationString + " does not match the data source.", "", 0);
                    return false;
                }
            }

            // If this code is reached, all of the columns are valid.
            return true;
        }
        #endregion

        #region FixOutputColumns
        private void FixOutputColumns()
        {
            IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
            DataTable dt = this.GetSchemaDataTable();

            ArrayList columnsToDelete = new ArrayList();

            // if a column exists in the ouput collection but doesn't exist at the data source remove it.
            // when a column exists in both places, verify that it's properties are set correctly.
            for (int x = 0; x < output.OutputColumnCollection.Count; x++)
            {
                IDTSOutputColumn90 col = output.OutputColumnCollection[x];
                IDTSCustomProperty90 dataSourceColumnName = col.CustomPropertyCollection["DataSourceColumnName"];

                int index = ColumnExists(dt, (string)dataSourceColumnName.Value);

                // Does the column exist in the external data source?
                if (index != -1)
                {
                    // Yes, do its properties match those of the output column?
                    if (!DataSourceColumnMatchesOutputColumn(col, dt.Rows[index]))
                    {
                        // No, fix it.
                        DataType dataType = new DataType();
                        int length = 0;
                        int precision = 0;
                        int scale = 0;
                        int codepage = 0;

                        GetDataSourceColumnProperties(ref dataType, ref length, ref scale, ref precision, ref codepage, dt.Rows[index]);
                        col.SetDataTypeProperties(dataType, length, precision, scale, codepage);
                    }
                }
                else
                {   
                    // No, it doesn't exist at the data source, so add it to the list of columns to delete.
                    // output.OutputColumnCollection.RemoveObjectByID(col.ID);
                    columnsToDelete.Add(col.ID);
                }
            }

            foreach (object o in columnsToDelete)
            {
                output.OutputColumnCollection.RemoveObjectByID((int)o);
            }

            // if a column exists at the data source but not in the collection, add it.
            foreach (DataRow row in dt.Rows)
            {
                if (!this.IsDataSourceColumnFound((string)row["ColumnName"]))
                {
                    this.CreateOutputColumn(row);
                }
            }
        }
        private bool IsDataSourceColumnFound(string DataSourceColumn)
        {
            IDTSOutput90 output = ComponentMetaData.OutputCollection[0];

            foreach (IDTSOutputColumn90 col in output.OutputColumnCollection)
            {
                if ((string)col.CustomPropertyCollection["DataSourceColumnName"].Value == DataSourceColumn)
                {
                    return true;
                }
            }

            return false;
        }
        #endregion

        #region DataSourceColumnMatchesOutputColumn
        private static bool DataSourceColumnMatchesOutputColumn(IDTSOutputColumn90 column, DataRow row)
        {
            DataType dt = new DataType();
            int length = 0;
            int scale = 0;
            int precision = 0;
            int codepage = 0;

            GetDataSourceColumnProperties(ref dt, ref length, ref scale, ref precision, ref codepage, row);

            if (dt == column.DataType && length == column.Length && scale == column.Scale && codepage == column.CodePage)
            {
                return true;
            }

            return false;
        }
        #endregion

        #region ColumnExists
        private static int ColumnExists(DataTable dt, string ColumnName)
        {
            for (int row = 0; row < dt.Rows.Count; row++)
            {
                if ((string)dt.Rows[row]["ColumnName"] == ColumnName)
                {
                    return row;
                }
            }

            return -1;
        }
        #endregion

        #region GetSchemaDataTable
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2201:DoNotRaiseReservedExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1303:DoNotPassLiteralsAsLocalizedParameters", MessageId = "System.Exception.#ctor(System.String)")]
        private DataTable GetSchemaDataTable()
        {
            if (this.isConnected 
                && this.oledbConnection != null)
            {
                try
                {
                    OleDbCommand cmd = this.oledbConnection.CreateCommand();
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = (string)ComponentMetaData.CustomPropertyCollection["SqlStatement"].Value;
                    OleDbDataReader schemaReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
                    return schemaReader.GetSchemaTable();
                }
                catch (System.Data.OleDb.OleDbException oleDbException)
                {
                    ComponentMetaData.FireError(
                        oleDbException.ErrorCode,
                        ComponentMetaData.Name,
                        "An OleDb exception occurred while retreiving the SchmaTable for \"" + (string)ComponentMetaData.CustomPropertyCollection["SqlStatement"].Value + "\".",
                        "",
                        0,
                        out this.Cancel);

                    foreach (OleDbError error in oleDbException.Errors)
                    {
                        ComponentMetaData.FireError(error.NativeError, ComponentMetaData.Name, error.Message, "", 0, out this.Cancel);
                    }

                    throw;
                }
            }
            else
            {
                throw new Exception("Unable to retrieve schema information from the data source either because the component is disconnected, or the connection is Nullable.");
            }
        }
        #endregion

        #region CreateColumnsFromDataTable
        /// <summary>
        /// This helper function creates the columns in the output and the external metadata. 
        /// It removes any existing columns before begining.
        /// It assumes a connection has been established.
        /// </summary>
        private void CreateColumnsFromDataTable()
        {
            // Get the output.
            IDTSOutput90 output = ComponentMetaData.OutputCollection[0];

            // Start clean.
            output.OutputColumnCollection.RemoveAll();
            output.ExternalMetadataColumnCollection.RemoveAll();

            // Get the schema table.
            DataTable dataTable = this.GetSchemaDataTable();

            // Walk the Columns in the schema and create output columns.
            foreach (DataRow row in dataTable.Rows)
            {
                this.CreateOutputColumn(row);
            }
        }
        #endregion

        #region CreateOutputColumn
        /// <summary>
        /// Creates an output column based on a column in the table schema.
        /// </summary>
        /// <param name="row"></param>
        private void CreateOutputColumn(DataRow row)
        {
            IDTSOutputColumn90 outColumn = ComponentMetaData.OutputCollection[0].OutputColumnCollection.New();
            IDTSCustomProperty90 dataSourceColumnName = outColumn.CustomPropertyCollection.New();
            dataSourceColumnName.Name = "DataSourceColumnName";
            dataSourceColumnName.Value = row["ColumnName"];
            dataSourceColumnName.Description = "The name of the column at the data source.";

            DataType dt = new DataType();
            int length = 0;
            int precision = 0;
            int scale = 0;
            int codepage = 0;
            GetDataSourceColumnProperties(ref dt, ref length, ref scale, ref precision, ref codepage, row);

            // Set the output column's properties.
            outColumn.Name = (string)row["ColumnName"];
            outColumn.SetDataTypeProperties(dt, length, precision, scale, codepage);

            CreateExternalMetaDataColumn(ComponentMetaData.OutputCollection[0].ExternalMetadataColumnCollection, outColumn);
        }
        #endregion

        #region GetDataSourceColumnProperties
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private static void GetDataSourceColumnProperties(ref DataType dt, ref int length, ref int scale, ref int precision, ref int codepage, DataRow row)
        {
            // Column data type properties
            bool isLong = false;
            dt = AdoSourceSample.DataRecordTypeToBufferType((Type)row["DataType"]);
            dt = ConvertBufferDataTypeToFitManaged(dt, ref isLong);

            // adjust the type if this columns is "long"
            if (((bool)row["IsLong"]) == true)
            {
                if (dt == DataType.DT_WSTR)
                {
                    dt = DataType.DT_NTEXT;
                }

                if (dt == DataType.DT_STR)
                {
                    dt = DataType.DT_TEXT;
                }

                if (dt == DataType.DT_BYTES)
                {
                    dt = DataType.DT_IMAGE;
                }
            }

            switch (dt)
            {
                // Length can not be zero, and the CodePage must contain a valid code page.
                case DataType.DT_STR:
                case DataType.DT_TEXT:
                case DataType.DT_BYTES:
                    length = (int)row["ColumnSize"];

                    if (dt != DataType.DT_BYTES)
                    {
                        codepage = row.Table.Locale.TextInfo.ANSICodePage;
                    }

                    break;

                case DataType.DT_WSTR:
                    length = (int)row["ColumnSize"];
                    break;

                case DataType.DT_NUMERIC:
                    // get the precision
                    try
                    {
                        precision = (int)Convert.ToInt16(
                            row["NumericPrecision"],
                            System.Globalization.CultureInfo.InvariantCulture);
                    }
                    catch
                    {
                        // the rowset doesn't give us precision, so use a default
                        precision = 28;
                    }

                    // get the scale
                    try
                    {
                        scale = (int)Convert.ToInt16(
                            row["NumericScale"],
                            System.Globalization.CultureInfo.InvariantCulture);
                    }
                    catch
                    {
                        // they didn't give a scale 
                        scale = 255;
                    }

                    // a decimal may not have a scale, so we make one up.
                    // this is a limitation of the managed source and user may need
                    // to adjust the scale 
                    if (scale == 255)
                    {
                        // if we can make it fit, make the worst case example 
                        if (precision <= 14)
                        {
                            scale = precision;
                            precision = scale * 2;
                        }
                        else
                        {
                            // set the precision to the max and use up the remaining space for the decimals
                            scale = 28 - precision;
                            precision = 28;

                            // this is somewhat arbitrary, but allow at least 4 digits of accuracy
                            if (scale < 5)
                            {
                                scale = 4;
                            }
                        }
                    }

                    break;
            }
        }
        #endregion

        #region CreateExternalMetaDataColumn
        /// <summary>
        /// Create an external metadata column for each output. Map the two 
        /// by setting the ExternalMetaDataColumnID property of the output to the external metadata column.
        /// </summary>
        /// <param name="output">The output the columns are added to.</param>
        private static void CreateExternalMetaDataColumn(IDTSExternalMetadataColumnCollection90 externalCollection, IDTSOutputColumn90 column)
        {
            // For each output column create an external meta data columns.
            IDTSExternalMetadataColumn90 eColumn = externalCollection.New();
            eColumn.Name = column.Name;
            eColumn.DataType = column.DataType;
            eColumn.Precision = column.Precision;
            eColumn.Length = column.Length;
            eColumn.Scale = column.Scale;

            column.ExternalMetadataColumnID = eColumn.ID;
        }
        #endregion

        #endregion
    }
}
